***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."

***Section auction range 
local date_begin = td(04jan2016)
local date_end   = td(29jul2019)


**********************************************************************
*********  DROP AUCTION DATA PRE 2016 & FEW TRADES WITH ISINS THAT DO NOT EXIST
**********************************************************************

*Drop auction data outside the sample 
drop if AuctionDate <`date_begin' & AuctionDate!=.
drop if AuctionDate >`date_end' & AuctionDate!=.

*To check first_auciton variable generated by Jason - can be deleted later on
bys isin market_type: egen a0 = min(AuctionDate) if market_type=="auction"
bys isin: egen checking = max(a0)
format checking %td
drop a0 


*clean out
replace Term = "50Y" if isin=="CA135087C939" & auctiontype=="BDREG"
		
*Drop isins that don't show up 
drop if  isin=="CA13520ZAA45" | isin=="CA13520ZAG15" ///
		| isin=="CA13520ZAH97" | isin=="CA13520ZCS35" /// 
		| isin=="CA13520ZDB90" | isin=="CA13520ZDD56" ///
		| isin=="CA13520ZDP86" | isin=="CA13520ZDQ69" ///
		| isin=="CA13522ZAF14" | isin=="CA13522ZDQ42" ///
		| isin=="CA13523ZAA18" | isin=="CA13523ZAB90" ///
		| isin=="CA13523ZAE30" | isin=="CA13523ZAF05" ///
		| isin=="CA13523ZAG87" | isin=="CA13523ZAH60" ///
		| isin=="CA13523ZAK99" | isin=="CA13523ZAL72" ///
		| isin=="CA13523ZAM55" | isin=="CA13523ZCP68" ///
		| isin=="CA13523ZCT80" | isin=="CA13523ZCV37" ///
		| isin=="CA13523ZCW10" |  isin=="CA13523ZDA80" ///
		| isin=="CA13523ZDB63" | isin=="CA13523ZDC47" ///
		| isin=="CA13523ZDD20" | isin=="CA13523ZDN02" /// 
		| isin=="CA13523ZDP59" |  isin=="CA13523ZDQ33" ///
		| isin=="CA13524ZAH51" |  isin=="CA13524ZDA71" ///
		| isin=="CA13524ZDE93" | isin=="CA13524ZDG42" /// 
		| isin=="CA13524ZDH25" |  isin=="CA13524ZDP41" /// 
		| isin=="CA13524ZDQ24" | isin=="CA1350Z7WB67" 
 
tempfile temp0
save  `temp0' , replace 


tab market_type
count if Term=="" & market_type=="auction"

 
******************************************************************************************
*** Auction data 
*** Data source: https://www.bankofcanada.ca/markets/government-securities-auctions/
******************************************************************************************

***1) T-Bill auctions
import excel "`dir'/0 public auctions/tbill_results.xlsx", sheet("T-bill") cellrange(A1:M1684) firstrow clear

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 10,30,0)
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Variables
gen Coupon = 0 

*Keep
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity Coupon IssueAmount

tempfile temp1
save  `temp1' , replace 


*** 2) Cash management bill auctions
import excel "`dir'/0 public auctions/cash_man_bill_results.xlsx", sheet("CMB") cellrange(A1:M505) firstrow clear

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 10,30,0)
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Variables
gen Coupon = 0 
gen Term="" 
replace Term="NF" if TermType=="Non-Fungible"
replace Term="CM" if TermType=="Fungible"

*Keep 
drop TermType
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity Coupon IssueAmount 

tempfile temp2
save  `temp2' , replace 


*** 3) Nominal bond auctions 
import excel "`dir'/0 public auctions/nominal_bond_results.xlsx", sheet("Nominal Bonds") cellrange(A1:O522) firstrow clear

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 12,00,0)
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Keep
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity IssueAmount Coupon OutstandingAfterAuction

tempfile temp3
save  `temp3' , replace 


*** 4) Cash management bond-buyback auctions
import excel "`dir'/0 public auctions/cash-management-bond-buyback-results.xlsx", sheet("CMBB") firstrow clear

*Fill in AuctionDate
replace AuctionDate = AuctionDate[_n-1] if AuctionDate==. 
replace Issue = Issue[_n-1] if Issue==.

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 11,00,0) // THIS IS ONLY TRUE FOR SOME AUCTIONS - THERE IS NOT UNIQUE DEADLNE
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Variables 
rename CouponRate Coupon 
gen Term="CM_bond"
rename TotalAllottedAmtSUM IssueAmount
replace TotalAmountRepurchased = TotalAmountRepurchased[_n-1] if TotalAmountRepurchased==.
replace TotalAmountRepurchased= -TotalAmountRepurchased
label var TotalAmountRepurchased "amount repurchased of all isins at auction - this is not isin specific!"

*Keep
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity IssueAmount Coupon TotalAmountRepurchased

tempfile temp4
save  `temp4' , replace 


*** 5) Real return auctions 
import excel "`dir'/0 public auctions/rrb_results.xlsx", sheet("RRB") cellrange(A1:M86) firstrow clear

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 11,00,0) // I DON'T KNOW THE DEADLINE HERE!!!
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Variables 
rename AmountOutstandingPostAuction OutstandingAfterAuction

*Keep 
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity IssueAmount Coupon OutstandingAfterAuction

tempfile temp5
save  `temp5' , replace 


*** 6) Switch operations
import excel "`dir'/0 public auctions/switch-operation-results.xlsx", sheet("Switch") firstrow clear

*Variables 
gen Term="SW_bond"
rename AmountRepurchased IssueAmount
rename Settlement Issue 
replace IssueAmount = - IssueAmount 
replace AuctionDate = AuctionDate[_n-1] if AuctionDate==. 
replace Issue = Issue[_n-1] if Issue==.

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 11,00,0) // I DON'T KNOW THE DEADLINE HERE!!!
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Keep
keep AuctionDate Issue ISIN Term Maturity IssueAmount Coupon 

tempfile temp6
save  `temp6' , replace 


*** 7) Ultra long auctions
import excel "`dir'/0 public auctions/ultra-long-bond-results.xlsx", sheet("Ultra Long") cellrange(A1:GM3) firstrow clear

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 12,00,0) 
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Variables
rename AmountOutstandingPostAuction OutstandingAfterAuction
rename TermType Term 

*Keep 
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity IssueAmount Coupon  OutstandingAfterAuction

tempfile temp7a
save  `temp7a' , replace 

import excel "`dir'/0 public auctions/ultra-long-bond-results.xlsx", sheet("Ultra Long Syndications") firstrow clear

*Variables 
rename PricingDate AuctionDate 

*Deadline according to the auciton-time line (https://www.bankofcanada.ca/wp-content/uploads/2010/10/auction_timelines_01042020.pdf)
gen double AuctionDeadline = .
replace AuctionDeadline = dhms(AuctionDate, 12,00,0) 
label var AuctionDeadline "Time of auction closure"
format AuctionDeadline %tc

*Keep
keep AuctionDeadline AuctionDate Issue ISIN Term Maturity IssueAmount Coupon  

tempfile temp7b
save  `temp7b' , replace 


*** APPEND
use  `temp1', clear 
append using `temp2'
append using `temp3'
append using `temp4'
append using `temp5'
append using `temp6'
append using `temp7a'
append using `temp7b'


*drop isins that mature before my sample  *--> important for first_auction because same isin name is repeated after years
count if Maturity==.
drop  if Maturity <`date_begin'

*drop auctions where nothign was issued or bought back
drop if IssueAmount==0 & (TotalAmountRepurchased==. | TotalAmountRepurchased==0)
replace TotalAmountRepurchased=0 if TotalAmountRepurchased==.

*** RENAME VARIABLES 
rename ISIN isin 
count if isin==""
rename Maturity Maturity_auc

	*is Maturity_auc unique per isin
	preserve
	collapse IssueAmount, by(isin Maturity_auc)
	bys isin: gen a0=_N
	tab a0  // should be 1 for all observations if isins are unique 
	restore

tempfile temp
save  `temp', replace
	
	
*********************************************************
*** AGGREGATE AUCTION VARIABLES 
*********************************************************

*check that there is a single auction of an isin in a day
preserve
collapse Issue, by(isin AuctionDate Term)
bys AuctionDate isin: gen a0=_N
tab a0 // should be 1 if no isin is auctioned twice in a day
restore 


*keep aggregated variables per auction 
collapse   IssueAmount Coupon OutstandingAfterAuction TotalAmountRepurchased Maturity_auc , by(isin  AuctionDeadline AuctionDate Term Issue)

tempfile tempauc
save  `tempauc', replace
	
	
*** MERGE WITH BASELINE DATA 
use  `temp0', clear

*keep only the auction data
keep if market_type=="auction"
merge m:1   isin AuctionDate Term  using  `tempauc'

drop if AuctionDate <`date_begin' // this is were baseline sample begins
drop if AuctionDate >`date_end'   // this is were auction data stops in baseline data corrently

	preserve
	collapse IssueAmount TotalAmountRepurchased issuesum no_buy* totalall* allotment_buy*, by(Term AuctionDate isin _merge)
	browse if _merge ==2
	browse isin AuctionDate Term issuesum no_buy* totalall* allotment_buy* if _merge==1
	restore
	

keep AuctionDeadline AuctionDate isin Term Issue IssueAmount Coupon OutstandingAfterAuction TotalAmountRepurchased Maturity_auc	
	
*Normalize all quantities by million	
replace IssueAmount 			= IssueAmount/10^6
replace OutstandingAfterAuction = OutstandingAfterAuction/10^6	
replace TotalAmountRepurchased  = TotalAmountRepurchased/10^6
	
	
tempfile tempauc2
save  `tempauc2', replace
	

*********************************************************
*** FIRST-AUCTION INDICATOR per isin
*********************************************************
	
use `temp', clear	
	
*** CREATE VARIABLE
bys isin: egen first_auction2 = min(AuctionDate)
format first_auction2 %td

collapse first_auction2 Maturity_auc, by(isin)

tempfile tempfinal
save  `tempfinal', replace


*** MERGE WITH BASELINE DATA 
use  `temp0', clear
merge m:1   isin   using  `tempfinal'


*drop securities that matured before my sample starts, or auctioned after my sample ends
drop if _merge==2 & Maturity_auc<`date_begin'
drop if _merge==2 & first_auction2>`date_end'


	tab isin if _merge==1
	tab isin if _merge==2 
	*---> should be 0


*Use first-auction variable based on auction data		
drop first_auction 
rename first_auction2 first_auction 
drop _merge 

*fill in infos for the missing RBB 
replace first_auction = td(13nov1991) 	if isin=="CA135087UL60"
replace Maturity = td(01dec2021)		if isin=="CA135087UL60"
replace outstanding = 5175 				if isin=="CA135087UL60" // 5175 million = 5.175 billion outstanding
replace coupon = 4.25 					if isin=="CA135087UL60"


*** MERGE IN AUCTION SETTLEMENT 
merge m:m isin AuctionDate Term using `tempauc2'
drop _merge 

*** Compare public auciton data and my auction data and correct some variables 

*Settlement dates never contradicting ?
count if  settlement_date!=Issue & market_type=="auction" & (Issue!=. & settlement_date !=.)
	*--> should be 0, then settlement dates are never contradicting

*Fill in missing settlement and standardSettlementDate	
replace settlement_date = Issue 						if settlement_date ==. & market_type=="auction"
replace standardSettlementDate = settlement_date 		if market_type=="auction" 
drop Issue // drop because this is redundant

*Correct the issue amount (seems wrong in my data)
browse AuctionDate isin Term date Issue IssueAmount issuesum market_type if market_type=="auction"
tab Term if  IssueAmount!=issuesum & issuesum!=. & market_type=="auction"
replace issuesum = IssueAmount if (Term=="CM_bond"  | Term=="SW_bond") & ( IssueAmount!=issuesum & issuesum!=. & market_type=="auction") 



